#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
# @Time   : 2022/3/28 15:26
# @Author : 闫峰
"""
import json

import openpyxl
import requests

import xlrd
import xlwt
import csv
from xlutils.copy import copy
from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
from utils.times_tool.time_control import now_time2


def get_excel_data(sheet_name: str, case_name: any) -> list:
    """
    读取 Excel 中的数据
    :param sheet_name: excel 中的 sheet 页的名称
    :param case_name: 测试用例名称
    :return:
    """
    res_list = []

    excel_dire = ensure_path_sep("\\Files\\E_Amazon_BrowseTree_Node_V2.xls")
    work_book = xlrd.open_workbook(excel_dire, formatting_info=True)

    # 打开对应的子表
    work_sheet = work_book.sheet_by_name(sheet_name)
    # 读取一行
    idx = 0
    for one in work_sheet.col_values(1):
        # 运行需要运行的测试用例
        if case_name in one:
            req_body_data = work_sheet.cell(idx, 9).value
            resp_data = work_sheet.cell(idx, 11).value
            res_list.append((req_body_data, json.loads(resp_data)))
        idx += 1
    return res_list


def set_excel_data(sheet_index: int) -> tuple:
    """
    excel 写入
    :return:
    """
    excel_dire = '../data/TestLogin.xls'
    work_book = xlrd.open_workbook(excel_dire, formatting_info=True)
    work_book_new = copy(work_book)

    work_sheet_new = work_book_new.get_sheet(sheet_index)
    return work_book_new, work_sheet_new


def read_excel_data(sheet_name, excle_dir):
    """
    读取 Excel 中的数据
    :param sheet_name: excel 中的 sheet 页的名称
    :param case_name: 测试用例名称
    :return:
    """
    res_list = []

    excel_dire = ensure_path_sep("\\Files\\" + excle_dir)
    work_book = xlrd.open_workbook(excel_dire, formatting_info=True)

    # 打开对应的子表
    work_sheet = work_book.sheet_by_name(sheet_name)
    select_list = work_sheet.col_values(1)

    for one in select_list:
        res_list.append(one)
    return res_list


def write_excle_data(excle_data, filedir):
    with open(file=filedir + 'TESTDATA-' + now_time2() + '.csv', mode='w', newline='', encoding='utf-8') as csvfile:
        write_excle = csv.writer(csvfile)
        if type(excle_data) == dict:
            write_excle.writerow(list(excle_data[0].keys()))

            row = 0
            for one in range(len(excle_data)):
                write_excle.writerow(list(excle_data[row].values()))
                row += 1
        elif type(excle_data) == list:
            row = 0
            for one in range(len(excle_data)):
                write_list = []
                write_list.append(excle_data[row])
                write_excle.writerow(write_list)
                row += 1


if __name__ == '__main__':

    from openpyxl import load_workbook
    from openpyxl import Workbook

    # workbook1 = Workbook()
    # worksheet1 = workbook1.active
    work_book = load_workbook(filename=r"D:\zhcx-service-test\data\JSON-1720536641509.xlsx")
    # work_book: openpyxl.workbook.workbook.Workbook
    # work_book.active
    # znkd_db = Database(ensure_path_sep("\\config\\znkd_mysql_config.yaml"), "znkd_database_prod")
    sheet1 = work_book.active  # 获取活动的 sheet1

    write_num = 0
    first_list = (
    'avatarUrl', 'avgCommissionRate', 'avgLiveGmv', 'avgLiveInteractionRate', 'avgLiveNumberViewers', 'avgVideoGmv',
    'brandCooperation', 'buyerGmv', 'celebrityId', 'celebrityLanguage', 'contactEmail', 'currency', 'dataTimeBegin',
    'dataTimeEnd', 'ecAvgLiveInteractionRate', 'ecAvgLiveNumberViewers', 'ecLiveCount', 'ecVideoCount',
    'ecVideoEngagement', 'ecVideoPlayCntMed', 'expertLikes', 'fans', 'celebrityId', 'topFollowerAge',
    'topFollowerAgeZb', 'follows', 'friends', 'id', 'likes', 'liveCount', 'liveGmv', 'liveGpm', 'medGmvRevenue',
    'nickName', 'platformId', 'ppKeywords', 'ppVideoNumber', 'ppVideoTime', 'ppVideoUrl', 'productItem',
    'productPriceRange', 'region', 'remark', 'sign', 'tikHomepage', 'topFollowerAge', 'topFollowerGender',
    'topFollowerGenderZb', 'totalTransactionVolume', 'transactionAmount', 'uid', 'uniqueId', 'unitsSold',
    'unitsSoldRange', 'upStatus', 'upTime', 'videoAvgViewCnt', 'videoCount', 'videoEngagement', 'videoGmv', 'videoGpm',
    'videoMedViewCnt', 'videos')
    def handler_data(data):
        res_dict = dict(zip(first_list,data))
        followerAgeList = []
        followerAgeList_one = {}
        for key in res_dict.keys():
            if key in ["celebrityId", "topFollowerAge", "topFollowerAgeZb"]:
                followerAgeList_one[key] = res_dict.get(key)
        res_dict.pop("celebrityId")
        res_dict.pop("topFollowerAge")
        res_dict.pop("topFollowerAgeZb")
        followerAgeList.append(followerAgeList_one)
        followerAgeList.append(followerAgeList_one)
        followerAgeList.append(followerAgeList_one)
        followerAgeList.append(followerAgeList_one)
        followerAgeList.append(followerAgeList_one)

        res_dict['followerAgeList'] = followerAgeList
        return res_dict


    res_list = []
    for row in sheet1.iter_rows(min_row=2, values_only=True):
        res_list.append(handler_data(row))
    with open(r"D:\zhcx-service-test\res_control.txt",'w',encoding='utf-8') as file:
        file.write(json.dumps(res_list,ensure_ascii=False))
        # 遍历 sheet1 中的每一行记录，从第二行开始遍历（第一行为表头）
    #     try:
    #         NationCode = row[0].lstrip("\t")
    #         SkuCode = row[1].lstrip("\t")
    #     except Exception as e:
    #         if row == (None, None):
    #             break
    #         else:
    #             print(row)
    #             continue
    #     get_price = """
    #     SELECT Price FROM `m_aliexpress_listing_pop_choice_product_sku_freightfee`
    #     WHERE SkuCode= %s
    #     AND NationCode= %s
    #     """
    #     price = znkd_db.fetch_all(get_price,(SkuCode,NationCode))
    #     if price:
    #         price = price[0]["Price"]
    #         data = (SkuCode,NationCode,price)
    #         worksheet1.append(data)
    #         write_num += 1
    #         print(f"写入数据{write_num}行")
    # workbook1.save('example3.xlsx')
